package com.felight.ems.sql;
import com.felight.ems.sql.ConnectionFactory;
import com.felight.examples.jdbc.db.DbUtil;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.log4j.Logger;

public class User {
		private int userId;
		private String userName;
		private String email;
		private String firstName;
		private String lastName;
		private String password;
		private char userType;
		private char activeFlag;
		private int createdBy;
		private Date createdDate;
		private int modifiedBy;
		private Date modifiedDate;
		private Date dob;
		private int coins;
		private int noOfBusiness;


		/*public User(int userId, String userName, String email,
				String firstName, String lastName, String password,
				char userType, char activeFlag, int createdBy,
				Date createdDate, int modifiedBy, Date modifiedDate, Date dob,
				int coins, int noOfBusiness) {
			super();
			this.userId = userId;
			this.userName = userName;
			this.email = email;
			this.firstName = firstName;
			this.lastName = lastName;
			this.password = password;
			this.userType = userType;
			this.activeFlag = activeFlag;
			this.createdBy = createdBy;
			this.createdDate = createdDate;
			this.modifiedBy = modifiedBy;
			this.modifiedDate = modifiedDate;
			this.dob = dob;
			this.coins = coins;
			this.noOfBusiness = noOfBusiness;
		}*/


		public int getUserId() {
			return userId;
		}


		public String getUserName() {
			return userName;
		}


		public String getEmail() {
			return email;
		}


		public String getFirstName() {
			return firstName;
		}


		public String getLastName() {
			return lastName;
		}


		public String getPassword() {
			return password;
		}


		public char getUserType() {
			return userType;
		}


		public char getActiveFlag() {
			return activeFlag;
		}


		public int getCreatedBy() {
			return createdBy;
		}


		public Date getCreatedDate() {
			return createdDate;
		}


		public int getModifiedBy() {
			return modifiedBy;
		}


		public Date getModifiedDate() {
			return modifiedDate;
		}


		public Date getDob() {
			return dob;
		}


		public int getCoins() {
			return coins;
		}


		public int getNoOfBusiness() {
			return noOfBusiness;
		}
		
		
		public void setUserId(int userId) {
			this.userId = userId;
		}


		public void setUserName(String userName) {
			this.userName = userName;
		}


		public void setEmail(String email) {
			this.email = email;
		}


		public void setFirstName(String firstName) {
			this.firstName = firstName;
		}


		public void setLastName(String lastName) {
			this.lastName = lastName;
		}


		public void setPassword(String password) {
			this.password = password;
		}


		public void setUserType(char string) {
			this.userType = string;
		}


		public void setActiveFlag(char string) {
			this.activeFlag = string;
		}


		public void setCreatedBy(int createdBy) {
			this.createdBy = createdBy;
		}


		public void setCreatedDate(Date createdDate) {
			this.createdDate = createdDate;
		}


		public void setModifiedBy(int modifiedBy) {
			this.modifiedBy = modifiedBy;
		}


		public void setModifiedDate(Date modifiedDate) {
			this.modifiedDate = modifiedDate;
		}


		public void setDob(Date dob) {
			this.dob = dob;
		}


		public void setCoins(int coins) {
			this.coins = coins;
		}


		public void setNoOfBusiness(int noOfBusiness) {
			this.noOfBusiness = noOfBusiness;
		}


		public class UserDAO{
			private Logger log = Logger.getLogger(UserDAO.class);
			private Connection connection;
			private Statement statement;
			private PreparedStatement preparedStatement;
		
			public User getUser(int userId){
				String query = "SELECT * FROM USER WHERE USER_ID = "+ userId;
				ResultSet rs = null;
				User user = null;
				try {
					connection = ConnectionFactory.getConnection();
					statement = connection.createStatement();
					rs = statement.executeQuery(query);
					if(rs.next()){
						user = new User();
						user.setUserId(rs.getInt("USER_ID"));
						user.setUserName(rs.getString("USER_NAME"));
						user.setEmail(rs.getString("EMAIL"));
						user.setFirstName(rs.getString("FIRST_NAME"));
						user.setLastName(rs.getString("LAST_NAME"));
						user.setPassword(rs.getString("PASSWORD"));
						user.setUserType(rs.getString("USER_TYPE"));
						user.setActiveFlag(rs.getString("ACTIVE_FLAG"));
						user.setCreatedBy(rs.getInt("CREATED_BY"));
						user.setCreatedDate(rs.getDate("CREATED_DATE"));
						user.setModifiedBy(rs.getInt("MODIFIED_BY"));
						user.setModifiedDate(rs.getDate("MODIFIED_DATE"));
						user.setDob(rs.getDate("DATE_OF_BIRTH"));
						user.setCoins(rs.getInt("COINS"));
						user.setNoOfBusiness(rs.getInt("NO_OF_BUSINESS"));
						log.info(query + "exectued successfully");
						return user;
					}
				}
				catch(SQLException e){
					log.debug("Select query unsuccessful : QUERY : "+query);
					log.debug("due to :"+e);
				}
				finally{
					DbUtil.close(rs);
					DbUtil.close(statement);
					DbUtil.close(connection);
				}
				return user;
			}
			public boolean saveUser(User user){
				String query = "INSERT INTO USER (USER_NAME, EMAIL, FIRST_NAME, LAST_NAME, PASSWORD, USER_TYPE, ACTIVE_FLAG, CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE, DATE_OF_BIRTH"
							+ "COINS, NO_OF_BUSINESS) VALUES"
							+ "(?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
				try{
					connection = ConnectionFactory.getConnection();
					preparedStatement = connection.prepareStatement(query);
					preparedStatement.setString(1, user.getUserName());
					preparedStatement.setString(2, user.getEmail());
					preparedStatement.setString(3, user.getFirstName());
					preparedStatement.setString(4, user.getLastName());
					preparedStatement.setString(5, user.getPassword());
					preparedStatement.setString(6, user.getUserType());
					preparedStatement.setString(7, user.getActiveFlag());
					preparedStatement.setInt(8, user.getCreatedBy());
					preparedStatement.setDate(9, user.getCreatedDate());
					preparedStatement.setInt(10, user.getModifiedBy());
					preparedStatement.setDate(11, user.getModifiedDate());
					preparedStatement.setDate(12, user.getDob());
					preparedStatement.setInt(13, user.getCoins());
					preparedStatement.setInt(14, user.getNoOfBusiness());
					log.info("NEw row added to database with query : "+ query);
					return preparedStatement.executeUpdate() > 0 ? true : false;
				}
				catch(SQLException ex){
					log.error("Insertion failed due to : "+ ex);
					return false;
				}
				finally{
					DbUtil.close(preparedStatement);
					DbUtil.close(connection);
				}
			}
			public boolean updateUser(User user){
				String query = "UPDATE USER SET USER_NAME = ?, EMAIL=?, FIRST_NAME=?, LAST_NAME=?,"
							+ "PASSWORD=?, USER_TYPE=?, ACTIVE_FLAG=?, CREATED_BY=?,"
							+"CREATED_DATE=?, MODIFIED_BY=?,MODIFIED_DATE=?,DATE_OF_BIRTH=?,"
							+"COINS=?,NO_OF_BUSINESS=? WHERE USER_ID="+user.getUserId();
				try{
					connection = ConnectionFactory.getConnection();
					preparedStatement = connection.prepareStatement(query);
					preparedStatement.setString(1, user.getUserName());
					preparedStatement.setString(2, user.getEmail());
					preparedStatement.setString(3, user.getFirstName());
					preparedStatement.setString(4, user.getLastName());
					preparedStatement.setString(5, user.getPassword());
					preparedStatement.setString(6, user.getUserType());
					preparedStatement.setString(7, user.getActiveFlag());
					preparedStatement.setInt(8, user.getCreatedBy());
					preparedStatement.setDate(9, user.getCreatedDate());
					preparedStatement.setInt(10, user.getModifiedBy());
					preparedStatement.setDate(11, user.getModifiedDate());
					preparedStatement.setDate(12, user.getDob());
					preparedStatement.setInt(13, user.getCoins());
					preparedStatement.setInt(14, user.getNoOfBusiness());
					log.info("Row updated successful with query :"+query);
					return preparedStatement.executeUpdate() >0 ? true : false;
				}
				catch(SQLException ex){
					log.error("Update failed due to :"+ ex);
					return false;
				}
				finally{
					DbUtil.close(preparedStatement);
					DbUtil.close(connection);
				}
			}
			public boolean deleteUser(User user){
				String query = "DELETE from USER WHERE USER_ID ="+user.getUserId();
				try{
					connection = ConnectionFactory.getConnection();
					preparedStatement = connection.prepareStatement(query);
					log.info("Row deleted successfully with query :"+ query);
					return preparedStatement.executeUpdate() > 0 ? true : false;
				}
				catch(SQLException ex){
					log.error("Delete failed due to : " + ex);
					return false;
				}
				finally {
					DbUtil.close(preparedStatement);
					DbUtil.close(connection);
				}
			}
		}
		
}


